BEGIN TRANSACTION;

ALTER TABLE [dbo].[projectmodification]
DROP COLUMN [previousvalue],[newvalue]

ALTER TABLE [dbo].[projectmodification]
ADD [modifiedfrom] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[modifiedto] varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[modifieddate] datetime NOT NULL ,
[modifiedby] int NOT NULL 

ALTER TABLE [dbo].[projectmodification] ADD CONSTRAINT [FK__cipeproject_modified_by] FOREIGN KEY ([modifiedby]) REFERENCES [dbo].[userinfo] ([id]) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE [dbo].[modificationtype] ADD CONSTRAINT [UNIQUE_MODE_TYPE_CODE] UNIQUE ([modificationtypecode])

INSERT INTO [cmrsfinal].[dbo].[modificationtype] ([modificationtypecode], [modificationdesc]) VALUES ('MTYPE_CODE8', 'Management Funds Increased')
INSERT INTO [cmrsfinal].[dbo].[modificationtype] ([modificationtypecode], [modificationdesc]) VALUES ('MTYPE_CODE9', 'Management Funds Reduced')

COMMIT TRANSACTION;
